--    Program: 		CAPPOEXT_ITEM.sql
--    Description:	Extract PO Item information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare
    l_processed_count		    number;
    l_rejected_count 		    number;
    l_err_msg				  	varchar2(2000);
    l_name				  		varchar2(200);
    
    l_filePath_itemsb           varchar2(200);
    l_filePath_itemstl          varchar2(200);
    l_fileName_itemsb           varchar2(100);
    l_fileName_itemstl          varchar2(100);
    
    l_set_of_books_id           number;
    
    cursor cur_items_b (p_set_of_books_id number) is
       select msib.* 
       from   mtl_system_items_b msib,
              org_organization_definitions ood
       where  msib.organization_id = ood.organization_id 
       and    ood.set_of_books_id = p_set_of_books_id;
              
    cursor cur_items_tl (p_set_of_books_id number) is
       select dtl.* 
       from   mtl_system_items_b msib, 
              mtl_system_items_tl dtl,
              org_organization_definitions ood
       where  msib.organization_id = ood.organization_id 
       and    ood.set_of_books_id = p_set_of_books_id
       and    msib.organization_id = dtl.organization_id
       and    msib.inventory_item_id = dtl.inventory_item_id;
 
    l_utl_file_b                  utl_file.file_type;
    l_utl_file_tl                 utl_file.file_type;
begin
   
   
   l_filePath_itemsb := '/tmp'  ;
   l_filePath_itemstl := '/tmp' ;
   
   l_fileName_itemsb := 'CAPPO_ITEM_B.dat';
   l_fileName_itemstl := 'CAPPO_ITEM_TL.dat';

   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_name := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_name);  
   exception
      when others then
        dbms_output.put_line(l_name||': set of books name not found');
   end; 
  
   l_utl_file_b := utl_file.fopen(l_filePath_itemsb,l_fileName_itemsb,'w');
   l_utl_file_tl := utl_file.fopen(l_filePath_itemstl,l_fileName_itemstl,'w');   
     
   for cur in cur_items_b(l_set_of_books_id) loop
       utl_file.put_line(l_utl_file_b,
       cur.INVENTORY_ITEM_ID              || chr(9) ||
       cur.ORGANIZATION_ID                || chr(9) ||
       cur.LAST_UPDATE_DATE               || chr(9) ||
       cur.LAST_UPDATED_BY                || chr(9) ||
       cur.CREATION_DATE                  || chr(9) ||
       cur.CREATED_BY                      || chr(9) ||
       cur.LAST_UPDATE_LOGIN               || chr(9) ||
       cur.SUMMARY_FLAG                    || chr(9) ||
       cur.ENABLED_FLAG                    || chr(9) ||
       cur.START_DATE_ACTIVE               || chr(9) ||
       cur.END_DATE_ACTIVE                 || chr(9) ||
       cur.DESCRIPTION                     || chr(9) ||
       cur.BUYER_ID                        || chr(9) ||
       cur.ACCOUNTING_RULE_ID              || chr(9) ||
       cur.INVOICING_RULE_ID               || chr(9) ||
       cur.SEGMENT1                        || chr(9) ||
       cur.SEGMENT2                        || chr(9) ||
       cur.SEGMENT3                        || chr(9) ||
       cur.SEGMENT4                        || chr(9) ||
       cur.SEGMENT5                        || chr(9) ||
       cur.SEGMENT6                        || chr(9) ||
       cur.SEGMENT7                        || chr(9) ||
       cur.SEGMENT8                        || chr(9) ||
       cur.SEGMENT9                        || chr(9) ||
       cur.SEGMENT10                       || chr(9) ||
       cur.SEGMENT11                       || chr(9) ||
       cur.SEGMENT12                       || chr(9) ||
       cur.SEGMENT13                       || chr(9) ||
       cur.SEGMENT14                       || chr(9) ||
       cur.SEGMENT15                       || chr(9) ||
       cur.SEGMENT16                       || chr(9) ||
       cur.SEGMENT17                       || chr(9) ||
       cur.SEGMENT18                       || chr(9) ||
       cur.SEGMENT19                       || chr(9) ||
       cur.SEGMENT20                       || chr(9) ||
       cur.ATTRIBUTE_CATEGORY              || chr(9) ||
       cur.ATTRIBUTE1                      || chr(9) ||
       cur.ATTRIBUTE2                      || chr(9) ||
       cur.ATTRIBUTE3                      || chr(9) ||
       cur.ATTRIBUTE4                      || chr(9) ||
       cur.ATTRIBUTE5                      || chr(9) ||
       cur.ATTRIBUTE6                      || chr(9) ||
       cur.ATTRIBUTE7                      || chr(9) ||
       cur.ATTRIBUTE8                      || chr(9) ||
       cur.ATTRIBUTE9                      || chr(9) ||
       cur.ATTRIBUTE10                     || chr(9) ||
       cur.ATTRIBUTE11                     || chr(9) ||
       cur.ATTRIBUTE12                     || chr(9) ||
       cur.ATTRIBUTE13                     || chr(9) ||
       cur.ATTRIBUTE14                     || chr(9) ||
       cur.ATTRIBUTE15                     || chr(9) ||
       cur.PURCHASING_ITEM_FLAG            || chr(9) ||
       cur.SHIPPABLE_ITEM_FLAG             || chr(9) ||
       cur.CUSTOMER_ORDER_FLAG             || chr(9) ||
       cur.INTERNAL_ORDER_FLAG             || chr(9) ||
       cur.SERVICE_ITEM_FLAG               || chr(9) ||
       cur.INVENTORY_ITEM_FLAG             || chr(9) ||
       cur.ENG_ITEM_FLAG                   || chr(9) ||
       cur.INVENTORY_ASSET_FLAG            || chr(9) ||
       cur.PURCHASING_ENABLED_FLAG         || chr(9) ||
       cur.CUSTOMER_ORDER_ENABLED_FLAG     || chr(9) ||
       cur.INTERNAL_ORDER_ENABLED_FLAG     || chr(9) ||
       cur.SO_TRANSACTIONS_FLAG            || chr(9) ||
       cur.MTL_TRANSACTIONS_ENABLED_FLAG   || chr(9) ||
       cur.STOCK_ENABLED_FLAG              || chr(9) ||
       cur.BOM_ENABLED_FLAG                || chr(9) ||
       cur.BUILD_IN_WIP_FLAG               || chr(9) ||
       cur.REVISION_QTY_CONTROL_CODE       || chr(9) ||
       cur.ITEM_CATALOG_GROUP_ID           || chr(9) ||
       cur.CATALOG_STATUS_FLAG             || chr(9) ||
       cur.RETURNABLE_FLAG                 || chr(9) ||
       cur.DEFAULT_SHIPPING_ORG            || chr(9) ||
       cur.COLLATERAL_FLAG                 || chr(9) ||
       cur.TAXABLE_FLAG                    || chr(9) ||
       cur.QTY_RCV_EXCEPTION_CODE          || chr(9) ||
       cur.ALLOW_ITEM_DESC_UPDATE_FLAG     || chr(9) ||
       cur.INSPECTION_REQUIRED_FLAG        || chr(9) ||
       cur.RECEIPT_REQUIRED_FLAG           || chr(9) ||
       cur.MARKET_PRICE                    || chr(9) ||
       cur.HAZARD_CLASS_ID                 || chr(9) ||
       cur.RFQ_REQUIRED_FLAG               || chr(9) ||
       cur.QTY_RCV_TOLERANCE               || chr(9) ||
       cur.LIST_PRICE_PER_UNIT             || chr(9) ||
       cur.UN_NUMBER_ID                    || chr(9) ||
       cur.PRICE_TOLERANCE_PERCENT         || chr(9) ||
       cur.ASSET_CATEGORY_ID               || chr(9) ||
       cur.ROUNDING_FACTOR                 || chr(9) ||
       cur.UNIT_OF_ISSUE                   || chr(9) ||
       cur.ENFORCE_SHIP_TO_LOCATION_CODE   || chr(9) ||
       cur.ALLOW_SUBSTITUTE_RECEIPTS_FLAG  || chr(9) ||
       cur.ALLOW_UNORDERED_RECEIPTS_FLAG   || chr(9) ||
       cur.ALLOW_EXPRESS_DELIVERY_FLAG     || chr(9) ||
       cur.DAYS_EARLY_RECEIPT_ALLOWED      || chr(9) ||
       cur.DAYS_LATE_RECEIPT_ALLOWED       || chr(9) ||
       cur.RECEIPT_DAYS_EXCEPTION_CODE     || chr(9) ||
       cur.RECEIVING_ROUTING_ID            || chr(9) ||
       cur.INVOICE_CLOSE_TOLERANCE         || chr(9) ||
       cur.RECEIVE_CLOSE_TOLERANCE         || chr(9) ||
       cur.AUTO_LOT_ALPHA_PREFIX           || chr(9) ||
       cur.START_AUTO_LOT_NUMBER           || chr(9) ||
       cur.LOT_CONTROL_CODE                || chr(9) ||
       cur.SHELF_LIFE_CODE                 || chr(9) ||
       cur.SHELF_LIFE_DAYS                 || chr(9) ||
       cur.SERIAL_NUMBER_CONTROL_CODE      || chr(9) ||
       cur.START_AUTO_SERIAL_NUMBER        || chr(9) ||
       cur.AUTO_SERIAL_ALPHA_PREFIX        || chr(9) ||
       cur.SOURCE_TYPE                     || chr(9) ||
       cur.SOURCE_ORGANIZATION_ID          || chr(9) ||
       cur.SOURCE_SUBINVENTORY             || chr(9) ||
       cur.EXPENSE_ACCOUNT                 || chr(9) ||
       cur.ENCUMBRANCE_ACCOUNT             || chr(9) ||
       cur.RESTRICT_SUBINVENTORIES_CODE    || chr(9) ||
       cur.UNIT_WEIGHT                     || chr(9) ||
       cur.WEIGHT_UOM_CODE                 || chr(9) ||
       cur.VOLUME_UOM_CODE                 || chr(9) ||
       cur.UNIT_VOLUME                     || chr(9) ||
       cur.RESTRICT_LOCATORS_CODE          || chr(9) ||
       cur.LOCATION_CONTROL_CODE           || chr(9) ||
       cur.SHRINKAGE_RATE                  || chr(9) ||
       cur.ACCEPTABLE_EARLY_DAYS           || chr(9) ||
       cur.PLANNING_TIME_FENCE_CODE        || chr(9) ||
       cur.DEMAND_TIME_FENCE_CODE          || chr(9) ||
       cur.LEAD_TIME_LOT_SIZE              || chr(9) ||
       cur.STD_LOT_SIZE                    || chr(9) ||
       cur.CUM_MANUFACTURING_LEAD_TIME     || chr(9) ||
       cur.OVERRUN_PERCENTAGE              || chr(9) ||
       cur.MRP_CALCULATE_ATP_FLAG          || chr(9) ||
       cur.ACCEPTABLE_RATE_INCREASE        || chr(9) ||
       cur.ACCEPTABLE_RATE_DECREASE        || chr(9) ||
       cur.CUMULATIVE_TOTAL_LEAD_TIME      || chr(9) ||
       cur.PLANNING_TIME_FENCE_DAYS        || chr(9) ||
       cur.DEMAND_TIME_FENCE_DAYS          || chr(9) ||
       cur.END_ASSEMBLY_PEGGING_FLAG       || chr(9) ||
       cur.REPETITIVE_PLANNING_FLAG        || chr(9) ||
       cur.PLANNING_EXCEPTION_SET          || chr(9) ||
       cur.BOM_ITEM_TYPE                   || chr(9) ||
       cur.PICK_COMPONENTS_FLAG            || chr(9) ||
       cur.REPLENISH_TO_ORDER_FLAG         || chr(9) ||
       cur.BASE_ITEM_ID                    || chr(9) ||
       cur.ATP_COMPONENTS_FLAG             || chr(9) ||
       cur.ATP_FLAG                        || chr(9) ||
       cur.FIXED_LEAD_TIME                 || chr(9) ||
       cur.VARIABLE_LEAD_TIME              || chr(9) ||
       cur.WIP_SUPPLY_LOCATOR_ID           || chr(9) ||
       cur.WIP_SUPPLY_TYPE                 || chr(9) ||
       cur.WIP_SUPPLY_SUBINVENTORY         || chr(9) ||
       cur.PRIMARY_UOM_CODE                || chr(9) ||
       cur.PRIMARY_UNIT_OF_MEASURE         || chr(9) ||
       cur.ALLOWED_UNITS_LOOKUP_CODE       || chr(9) ||
       cur.COST_OF_SALES_ACCOUNT           || chr(9) ||
       cur.SALES_ACCOUNT                   || chr(9) ||
       cur.DEFAULT_INCLUDE_IN_ROLLUP_FLAG  || chr(9) ||
       cur.INVENTORY_ITEM_STATUS_CODE      || chr(9) ||
       cur.INVENTORY_PLANNING_CODE         || chr(9) ||
       cur.PLANNER_CODE                    || chr(9) ||
       cur.PLANNING_MAKE_BUY_CODE          || chr(9) ||
       cur.FIXED_LOT_MULTIPLIER            || chr(9) ||
       cur.ROUNDING_CONTROL_TYPE           || chr(9) ||
       cur.CARRYING_COST                   || chr(9) ||
       cur.POSTPROCESSING_LEAD_TIME        || chr(9) ||
       cur.PREPROCESSING_LEAD_TIME         || chr(9) ||
       cur.FULL_LEAD_TIME                  || chr(9) ||
       cur.ORDER_COST                      || chr(9) ||
       cur.MRP_SAFETY_STOCK_PERCENT        || chr(9) ||
       cur.MRP_SAFETY_STOCK_CODE           || chr(9) ||
       cur.MIN_MINMAX_QUANTITY             || chr(9) ||
       cur.MAX_MINMAX_QUANTITY             || chr(9) ||
       cur.MINIMUM_ORDER_QUANTITY          || chr(9) ||
       cur.FIXED_ORDER_QUANTITY            || chr(9) ||
       cur.FIXED_DAYS_SUPPLY               || chr(9) ||
       cur.MAXIMUM_ORDER_QUANTITY          || chr(9) ||
       cur.ATP_RULE_ID                     || chr(9) ||
       cur.PICKING_RULE_ID                 || chr(9) ||
       cur.RESERVABLE_TYPE                 || chr(9) ||
       cur.POSITIVE_MEASUREMENT_ERROR      || chr(9) ||
       cur.NEGATIVE_MEASUREMENT_ERROR      || chr(9) ||
       cur.ENGINEERING_ECN_CODE            || chr(9) ||
       cur.ENGINEERING_ITEM_ID             || chr(9) ||
       cur.ENGINEERING_DATE                || chr(9) ||
       cur.SERVICE_STARTING_DELAY          || chr(9) ||
       cur.VENDOR_WARRANTY_FLAG            || chr(9) ||
       cur.SERVICEABLE_COMPONENT_FLAG      || chr(9) ||
       cur.SERVICEABLE_PRODUCT_FLAG        || chr(9) ||
       cur.BASE_WARRANTY_SERVICE_ID        || chr(9) ||
       cur.PAYMENT_TERMS_ID                || chr(9) ||
       cur.PREVENTIVE_MAINTENANCE_FLAG     || chr(9) ||
       cur.PRIMARY_SPECIALIST_ID           || chr(9) ||
       cur.SECONDARY_SPECIALIST_ID         || chr(9) ||
       cur.SERVICEABLE_ITEM_CLASS_ID       || chr(9) ||
       cur.TIME_BILLABLE_FLAG              || chr(9) ||
       cur.MATERIAL_BILLABLE_FLAG          || chr(9) ||
       cur.EXPENSE_BILLABLE_FLAG           || chr(9) ||
       cur.PRORATE_SERVICE_FLAG            || chr(9) ||
       cur.COVERAGE_SCHEDULE_ID            || chr(9) ||
       cur.SERVICE_DURATION_PERIOD_CODE    || chr(9) ||
       cur.SERVICE_DURATION                || chr(9) ||
       cur.WARRANTY_VENDOR_ID              || chr(9) ||
       cur.MAX_WARRANTY_AMOUNT             || chr(9) ||
       cur.RESPONSE_TIME_PERIOD_CODE       || chr(9) ||
       cur.RESPONSE_TIME_VALUE             || chr(9) ||
       cur.NEW_REVISION_CODE               || chr(9) ||
       cur.INVOICEABLE_ITEM_FLAG           || chr(9) ||
       cur.TAX_CODE                        || chr(9) ||
       cur.INVOICE_ENABLED_FLAG            || chr(9) ||
       cur.MUST_USE_APPROVED_VENDOR_FLAG   || chr(9) ||
       cur.REQUEST_ID                      || chr(9) ||
       cur.PROGRAM_APPLICATION_ID          || chr(9) ||
       cur.PROGRAM_ID                      || chr(9) ||
       cur.PROGRAM_UPDATE_DATE             || chr(9) ||
       cur.OUTSIDE_OPERATION_FLAG          || chr(9) ||
       cur.OUTSIDE_OPERATION_UOM_TYPE      || chr(9) ||
       cur.SAFETY_STOCK_BUCKET_DAYS        || chr(9) ||
       cur.AUTO_REDUCE_MPS                 || chr(9) ||
       cur.COSTING_ENABLED_FLAG            || chr(9) ||
       cur.AUTO_CREATED_CONFIG_FLAG        || chr(9) ||
       cur.CYCLE_COUNT_ENABLED_FLAG        || chr(9) ||
       cur.ITEM_TYPE                       || chr(9) ||
       cur.MODEL_CONFIG_CLAUSE_NAME        || chr(9) ||
       cur.SHIP_MODEL_COMPLETE_FLAG        || chr(9) ||
       cur.MRP_PLANNING_CODE               || chr(9) ||
       cur.RETURN_INSPECTION_REQUIREMENT   || chr(9) ||
       cur.ATO_FORECAST_CONTROL            || chr(9) ||
       cur.RELEASE_TIME_FENCE_CODE         || chr(9) ||
       cur.RELEASE_TIME_FENCE_DAYS         || chr(9) ||
       cur.CONTAINER_ITEM_FLAG             || chr(9) ||
       cur.VEHICLE_ITEM_FLAG               || chr(9) ||
       cur.MAXIMUM_LOAD_WEIGHT             || chr(9) ||
       cur.MINIMUM_FILL_PERCENT            || chr(9) ||
       cur.CONTAINER_TYPE_CODE             || chr(9) ||
       cur.INTERNAL_VOLUME                 || chr(9) ||
       cur.WH_UPDATE_DATE                  || chr(9) ||
       cur.PRODUCT_FAMILY_ITEM_ID          || chr(9) ||
       cur.GLOBAL_ATTRIBUTE_CATEGORY       || chr(9) ||
       cur.GLOBAL_ATTRIBUTE1               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE2               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE3               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE4               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE5               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE6               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE7               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE8               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE9               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE10              || chr(9) ||
       cur.PURCHASING_TAX_CODE             || chr(9) ||
       cur.OVERCOMPLETION_TOLERANCE_TYPE   || chr(9) ||
       cur.OVERCOMPLETION_TOLERANCE_VALUE  || chr(9) ||
       cur.EFFECTIVITY_CONTROL             || chr(9) ||
       cur.CHECK_SHORTAGES_FLAG            || chr(9) ||
       cur.OVER_SHIPMENT_TOLERANCE         || chr(9) ||
       cur.UNDER_SHIPMENT_TOLERANCE        || chr(9) ||
       cur.OVER_RETURN_TOLERANCE           || chr(9) ||
       cur.UNDER_RETURN_TOLERANCE          || chr(9) ||
       cur.EQUIPMENT_TYPE                  || chr(9) ||
       cur.RECOVERED_PART_DISP_CODE        || chr(9) ||
       cur.DEFECT_TRACKING_ON_FLAG         || chr(9) ||
       cur.USAGE_ITEM_FLAG                 || chr(9) ||
       cur.EVENT_FLAG                      || chr(9) ||
       cur.ELECTRONIC_FLAG                 || chr(9) ||
       cur.DOWNLOADABLE_FLAG               || chr(9) ||
       cur.VOL_DISCOUNT_EXEMPT_FLAG        || chr(9) ||
       cur.COUPON_EXEMPT_FLAG              || chr(9) ||
       cur.COMMS_NL_TRACKABLE_FLAG         || chr(9) ||
       cur.ASSET_CREATION_CODE             || chr(9) ||
       cur.COMMS_ACTIVATION_REQD_FLAG      || chr(9) ||
       cur.ORDERABLE_ON_WEB_FLAG           || chr(9) ||
       cur.BACK_ORDERABLE_FLAG             || chr(9) ||
       cur.WEB_STATUS                      || chr(9) ||
       cur.INDIVISIBLE_FLAG                || chr(9) ||
       cur.DIMENSION_UOM_CODE              || chr(9) ||
       cur.UNIT_LENGTH                     || chr(9) ||
       cur.UNIT_WIDTH                      || chr(9) ||
       cur.UNIT_HEIGHT                     || chr(9) ||
       cur.BULK_PICKED_FLAG                || chr(9) ||
       cur.LOT_STATUS_ENABLED              || chr(9) ||
       cur.DEFAULT_LOT_STATUS_ID           || chr(9) ||
       cur.SERIAL_STATUS_ENABLED           || chr(9) ||
       cur.DEFAULT_SERIAL_STATUS_ID        || chr(9) ||
       cur.LOT_SPLIT_ENABLED               || chr(9) ||
       cur.LOT_MERGE_ENABLED               || chr(9) ||
       cur.INVENTORY_CARRY_PENALTY         || chr(9) ||
       cur.OPERATION_SLACK_PENALTY         || chr(9) ||
       cur.FINANCING_ALLOWED_FLAG          || chr(9) ||
       cur.EAM_ITEM_TYPE                   || chr(9) ||
       cur.EAM_ACTIVITY_TYPE_CODE          || chr(9) ||
       cur.EAM_ACTIVITY_CAUSE_CODE         || chr(9) ||
       cur.EAM_ACT_NOTIFICATION_FLAG       || chr(9) ||
       cur.EAM_ACT_SHUTDOWN_STATUS         || chr(9) ||
       cur.DUAL_UOM_CONTROL                || chr(9) ||
       cur.SECONDARY_UOM_CODE              || chr(9) ||
       cur.DUAL_UOM_DEVIATION_HIGH         || chr(9) ||
       cur.DUAL_UOM_DEVIATION_LOW          || chr(9) ||
       cur.CONTRACT_ITEM_TYPE_CODE         || chr(9) ||
       cur.SUBSCRIPTION_DEPEND_FLAG        || chr(9) ||
       cur.SERV_REQ_ENABLED_CODE           || chr(9) ||
       cur.SERV_BILLING_ENABLED_FLAG       || chr(9) ||
       cur.SERV_IMPORTANCE_LEVEL           || chr(9) ||
       cur.PLANNED_INV_POINT_FLAG          || chr(9) ||
       cur.LOT_TRANSLATE_ENABLED           || chr(9) ||
       cur.DEFAULT_SO_SOURCE_TYPE          || chr(9) ||
       cur.CREATE_SUPPLY_FLAG              || chr(9) ||
       cur.SUBSTITUTION_WINDOW_CODE        || chr(9) ||
       cur.SUBSTITUTION_WINDOW_DAYS        || chr(9) ||
       cur.IB_ITEM_INSTANCE_CLASS          || chr(9) ||
       cur.CONFIG_MODEL_TYPE               || chr(9) ||
       cur.LOT_SUBSTITUTION_ENABLED        || chr(9) ||
       cur.MINIMUM_LICENSE_QUANTITY        || chr(9) ||
       cur.EAM_ACTIVITY_SOURCE_CODE        || chr(9) ||
       cur.LIFECYCLE_ID                    || chr(9) ||
       cur.CURRENT_PHASE_ID                || chr(9) ||
       cur.OBJECT_VERSION_NUMBER                    
       );   
    l_processed_count := l_processed_count + 1;
  end loop;

  if l_processed_count = 0 then   
    l_rejected_count := l_rejected_count + 1;
    l_err_msg := 'PO Item not found';
  end if;
   
  -- insert into log table
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ('CAPPOEXT_ITEM_B', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
  l_processed_count := 0;
  l_rejected_count := 0;
  l_err_msg := '';

   for cur1 in cur_items_tl (l_set_of_books_id) loop
       utl_file.put_line(l_utl_file_tl,
          cur1.INVENTORY_ITEM_ID || chr(9) || 
          cur1.ORGANIZATION_ID   || chr(9) || 
          cur1.LANGUAGE          || chr(9) || 
          cur1.SOURCE_LANG       || chr(9) || 
          cur1.DESCRIPTION       || chr(9) || 
          cur1.LAST_UPDATE_DATE  || chr(9) || 
          cur1.LAST_UPDATED_BY   || chr(9) || 
          cur1.CREATION_DATE     || chr(9) || 
          cur1.CREATED_BY        || chr(9) || 
          cur1.LAST_UPDATE_LOGIN || chr(9) || 
          cur1.LONG_DESCRIPTION   
       );
   l_processed_count := l_processed_count + 1;
  end loop;

  if l_processed_count = 0 then   
    l_rejected_count := l_rejected_count + 1;
    l_err_msg := 'PO Item not found';
  end if;
   
  -- insert into log table
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ('CAPPOEXT_ITEM_TL', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
  commit;

  utl_file.fclose(l_utl_file_b);
  utl_file.fclose(l_utl_file_tl);
   
exception
    when others then
         dbms_output.put_line(sqlerrm);
         rollback;
   
end;
/


--exit;
